import pandas as pd
import numpy as np
import pandas as pd
# Read the Excel file
df = pd.read_excel('Datasets/ElectricityByCounty.xlsx')
# Reshape the DataFrame
df = df.melt(id_vars=['County', 'Sector'],
var_name='Timestamp', value_name='Yearly Data')
# Convert the "Timestamp" column to datetime format representing the year
df['Timestamp'] = pd.to_datetime(df['Timestamp'], format='%Y')
# Sort the DataFrame by County and Timestamp
df = df.sort_values(['County', 'Timestamp'])
# Print the resulting DataFrame
df_indexd = df.set_index('Timestamp')
df_indexd
| County | Sector | Yearly Data | |
|---|---|---|---|
| Timestamp | |||
| 1990-01-01 | ALAMEDA | Non-Residential | 7109.299895 |
| 1990-01-01 | ALAMEDA | Residential | 2498.265626 |
| 1990-01-01 | ALAMEDA | Total | 9607.565521 |
| 1991-01-01 | ALAMEDA | Non-Residential | 6809.573768 |
| 1991-01-01 | ALAMEDA | Residential | 2515.209970 |
| ... | ... | ... | ... |
| 2020-01-01 | YUBA | Residential | 251.861806 |
| 2020-01-01 | YUBA | Total | 567.007959 |
| 2021-01-01 | YUBA | Non-Residential | 315.822249 |
| 2021-01-01 | YUBA | Residential | 260.500675 |
| 2021-01-01 | YUBA | Total | 576.322924 |
5568 rows × 3 columns
import pandas as pd
import plotly.graph_objects as go
from statsmodels.tsa.arima.model import ARIMA
from dateutil.relativedelta import relativedelta
import numpy as np
from pmdarima import auto_arima
# Read the Excel file
df = pd.read_excel('Datasets/ElectricityByCounty.xlsx')
# Reshape the DataFrame
df = df.melt(id_vars=['County', 'Sector'],
var_name='Timestamp', value_name='Yearly Data')
# Convert the "Timestamp" column to datetime format representing the year
df['Timestamp'] = pd.to_datetime(df['Timestamp'], format='%Y')
# Sort the DataFrame by County and Timestamp
df = df.sort_values(['County', 'Timestamp'])
# Iterate over each country and sector
for country in df['County'].unique():
for sector in df['Sector'].unique():
fig = go.Figure()
# Get the energy consumption data for the current country and sector
df_filter = df[(df['County'] == country) & (
df['Sector'] == sector)][['Timestamp', 'Yearly Data']]
df_filter_index = df_filter.set_index('Timestamp')
# Prepare the data for modeling
years = df_filter_index.index
energy_consumption = df_filter_index.values.flatten()
# Split the data into training and testing
# Use all data except the last 5 years for training
Horizan = -5
train_data = energy_consumption[:Horizan]
test_data = energy_consumption[Horizan:] # Use the last 5 years for testing
# Fit the auto ARIMA model
model = auto_arima(train_data, seasonal=False)
model.fit(train_data)
# Generate predictions
predictions = model.predict(n_periods=len(test_data))
predictions_ahead_in_future = model.predict(n_periods=len(test_data)+16)
# Plot the training data
fig.add_trace(go.Scatter(
x=years[:Horizan], y=train_data, mode='lines+markers', name='Training Data'))
# Plot the predictions
fig.add_trace(go.Scatter(
x=years[Horizan:], y=test_data, mode='lines+markers', name='Testing Data'))
fig.add_trace(go.Scatter(
x=years[Horizan:], y=predictions, mode='lines+markers', name='Predictions'))
fig.add_trace(go.Scatter(
x=pd.date_range(start = years[Horizan],periods=16,freq='Y'), y=predictions_ahead_in_future, mode='lines+markers', name='Ahead in Future Predictions'))
# Update the layout
fig.update_layout(title=f'Energy Consumption Forecast Country : {country} : Sector {sector} ',
xaxis_title='Year', yaxis_title='Energy Consumption')
# Show the plot
fig.show()
break
pd.date_range(start = years[Horizan],periods=30,freq='Y')
DatetimeIndex(['2017-12-31', '2018-12-31', '2019-12-31', '2020-12-31',
'2021-12-31', '2022-12-31', '2023-12-31', '2024-12-31',
'2025-12-31', '2026-12-31', '2027-12-31', '2028-12-31',
'2029-12-31', '2030-12-31', '2031-12-31', '2032-12-31',
'2033-12-31', '2034-12-31', '2035-12-31', '2036-12-31',
'2037-12-31', '2038-12-31', '2039-12-31', '2040-12-31',
'2041-12-31', '2042-12-31', '2043-12-31', '2044-12-31',
'2045-12-31', '2046-12-31'],
dtype='datetime64[ns]', freq='A-DEC')